package com.anycl.customer.dao.impl;

import com.anycl.customer.constant.Constants;
import com.anycl.customer.dao.CustomerRepository;
import com.anycl.customer.entity.Customer;
import com.anycl.customer.utils.JDBCUtil;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class CustomerRepositoryImpl implements CustomerRepository {
    @Override
    public int insertCustomer(Customer customer) {
        Connection conn = null;
        PreparedStatement pst = null;
        String sql = "insert into customer(name, address, telephone, email, status, remark, create_time, update_time)" +
                " values(?,?,?,?,?,?,?,?)";
        log.info(sql);
        int key = 0;
        try {
            conn = JDBCUtil.getConnection();
            pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pst.setString(1, customer.getName());
            pst.setString(2, customer.getAddress());
            pst.setString(3, customer.getTelephone());
            pst.setString(4, customer.getEmail());
            pst.setInt(5, customer.getStatus());
            pst.setString(6, customer.getRemark());
            pst.setTimestamp(7, customer.getCreateTime());
            pst.setTimestamp(8, customer.getUpdateTime());
            pst.executeUpdate();
            ResultSet rsKey = pst.getGeneratedKeys(); //ResultSet 指示键值
            rsKey.next();
            key = rsKey.getInt(1); //得到第一个键值
            log.info("key " + key);
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(pst, conn);
        }
        return key;
    }

    @Override
    public int deleteCustomerById(Long id) {
        // 逻辑删除
        Connection conn = null;
        PreparedStatement pst = null;
        String sql = "update customer set status = " + Constants.NOT_OK_STATUS + " where id = " + id;
        log.info(sql);
        int num = 0;
        try {
            conn = JDBCUtil.getConnection();
            pst = conn.prepareStatement(sql);
            num = pst.executeUpdate();
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(pst, conn);
        }
        return num;
    }

    @Override
    public int updateCustomer(Customer customer) {
        Connection conn = null;
        PreparedStatement pst = null;
        String sql = "update customer set name = ?, address = ?, telephone = ?, email = ?, status = ?, remark = ?, create_time = ?, update_time = ? where id = ?";
        log.info(sql);
        int num = 0;
        try {
            conn = JDBCUtil.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setString(1, customer.getName());
            pst.setString(2, customer.getAddress());
            pst.setString(3, customer.getTelephone());
            pst.setString(4, customer.getEmail());
            pst.setInt(5, customer.getStatus());
            pst.setString(6, customer.getRemark());
            pst.setTimestamp(7, customer.getCreateTime());
            pst.setTimestamp(8, customer.getUpdateTime());
            pst.setLong(9, customer.getId());
            num = pst.executeUpdate();
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(pst, conn);
        }
        return num;
    }

    @Override
    public Customer selectCustomerById(Long id) {
        Customer customer = null;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        String sql = "select id, name, address, telephone, email, status, remark, create_time, update_time from customer where id = ?";
        log.info(sql);
        try {
            conn = JDBCUtil.getConnection();
            pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pst.setLong(1, id);
            rs = pst.executeQuery();

            while(rs.next()) {
                customer = new Customer();
                customer.setId(rs.getLong(1));
                customer.setName(rs.getString(2));
                customer.setAddress(rs.getString(3));
                customer.setTelephone(rs.getString(4));
                customer.setEmail(rs.getString(5));
                customer.setStatus(rs.getInt(6));
                customer.setRemark(rs.getString(7));
                customer.setCreateTime(rs.getTimestamp(8));
                customer.setUpdateTime(rs.getTimestamp(9));
            }
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, pst, conn);
        }

        return customer;
    }

    @Override
    public List<Customer> selectAllCustomer() {
        List<Customer> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        String sql = "select id, name, address, telephone, email, status, remark, create_time, update_time from customer where status = " + Constants.OK_STATUS + "";
        log.info(sql);
        try {
            conn = JDBCUtil.getConnection();
            pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            rs = pst.executeQuery();

            while(rs.next()) {
                Customer customer = new Customer();
                customer.setId(rs.getLong(1));
                customer.setName(rs.getString(2));
                customer.setAddress(rs.getString(3));
                customer.setTelephone(rs.getString(4));
                customer.setEmail(rs.getString(5));
                customer.setStatus(rs.getInt(6));
                customer.setRemark(rs.getString(7));
                customer.setCreateTime(rs.getTimestamp(8));
                customer.setUpdateTime(rs.getTimestamp(9));
                list.add(customer);
            }
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.release(rs, pst, conn);
        }

        return list;
    }
}
